﻿using System;
using System.Data;
using System.Collections;
using CE.Database;

/// <summary>
/// Summary description for EtiqaProposalDB
/// </summary>
/// 
namespace CE.PersonalAccident
{
    public class EtiqaProposalDB
    {
        public EtiqaProposalDB()
        {
            //
            // TODO: Add constructor logic here
            //
        }

        public Int64 Insert(EtiqaProposal etiqaproposal)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "sprocPAProposalInsertUpdateSingleItemEtiqa";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@PAProposalId", etiqaproposal.PAProposalId);
            _sPara.Add("@PAId", etiqaproposal.PAId);
            _sPara.Add("@PACompanyId", etiqaproposal.PACompanyId);
            _sPara.Add("@PAInsId", etiqaproposal.PAInsId);
            _sPara.Add("@Salutation", etiqaproposal.Salutation);
            _sPara.Add("@Name", etiqaproposal.Name);
            _sPara.Add("@PostalCode", etiqaproposal.PostalCode);
            _sPara.Add("@UnitNo", etiqaproposal.UnitNo);
            _sPara.Add("@HomeNo", etiqaproposal.HomeNo);
            _sPara.Add("@Address", etiqaproposal.Address);
            _sPara.Add("@Gender", etiqaproposal.Gender);
            _sPara.Add("@NRICNo", etiqaproposal.NRICNo);
            _sPara.Add("@Nationality", etiqaproposal.Nationality);
            _sPara.Add("@MobileNo", etiqaproposal.MobileNo);
            _sPara.Add("@OfficePhoneNo", etiqaproposal.OfficePhoneNo);
            _sPara.Add("@HomePhoneNo", etiqaproposal.HomePhoneNo);
            _sPara.Add("@Email", etiqaproposal.Email);
            _sPara.Add("@NatureofBusiness", etiqaproposal.NatureofBusiness);
            _sPara.Add("@CompanyROCNo", etiqaproposal.CompanyROCNo);
            _sPara.Add("@SpouseName", etiqaproposal.SpouseName);
            _sPara.Add("@SpouseGender", etiqaproposal.SpouseGender);
            _sPara.Add("@SpouseNRICNo", etiqaproposal.SpouseNRICNo);
            _sPara.Add("@SpouseNationality", etiqaproposal.SpouseNationality);
            _sPara.Add("@SpouseRelation", etiqaproposal.SpouseRelation);
            _sPara.Add("@HasEngagedSportsActivities", etiqaproposal.HasEngagedSportsActivities);
            _sPara.Add("@EngagedSportsActivities", etiqaproposal.EngagedSportsActivities);
            _sPara.Add("@HasPhysicalDefects", etiqaproposal.HasPhysicalDefects);
            _sPara.Add("@PhysicalDefects", etiqaproposal.PhysicalDefects);
            _sPara.Add("@HasInsuranceCancelled", etiqaproposal.HasInsuranceCancelled);
            _sPara.Add("@InsuranceCancelledDetails", etiqaproposal.InsuranceCancelledDetails);
            _sPara.Add("@PamentType", etiqaproposal.PaymentType);
            _sPara.Add("@OrderNo", etiqaproposal.OrderNo);
            _sPara.Add("@OrderSerialNo", etiqaproposal.OrderSerialNo);
            _sPara.Add("@PaymentStatus", etiqaproposal.PaymentStatus);
            _sPara.Add("@QuoteRefNo", etiqaproposal.QuoteRefNo);
            _sPara.Add("@RefCEUserId", etiqaproposal.RefCEUserId);
            _sPara.Add("@ViewedDetailId", etiqaproposal.ViewedDetailId);
            _sPara.Add("@NInfo1", etiqaproposal.NInfo1);
            _sPara.Add("@Status", etiqaproposal.Status);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 UpdateStatus(EtiqaProposal etiqaproposal)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "[sprocPASelectedItemStatusUpdateSingleItemEtiqa]";

            SortedList _sPara = new SortedList();

            #region Parameter
            _sPara.Add("@OrderNo", etiqaproposal.OrderNo);
            _sPara.Add("@FilePath", etiqaproposal.Info10);
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public Int64 UpdateSelectStatus(EtiqaProposal etiqaproposal)
        {
            MyDatabase _db = new MyDatabase();
            Int64 _result = -1;
            string _spName = "[sprocSelectedPADetailInsertUpdateSingleItem]";

            SortedList _sPara = new SortedList();

            #region Parameter

            _sPara.Add("@PAId", etiqaproposal.PAId);
            
            #endregion

            _result = _db.ExecuteSP(_spName, _sPara);

            return _result;
        }

        public bool Update()
        {
            return true;
        }

        public bool Delete()
        {
            return true;
        }

        public EtiqaProposal GetPAMoreDetail(Int64 PAId, Int64 CEUserId)
        {
            EtiqaProposal _etiqaproposal = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocPAProposalSelectSingleItemEtiqa]";

            SortedList _spara = new SortedList();
            _spara.Add("@PAId", PAId);
            _spara.Add("@RefCEUserId", CEUserId);

            DataTable _dt = _db.GetDataTableBySP(_spName, _spara);
            if (_dt != null)
            {
                _etiqaproposal = new EtiqaProposal();
                _etiqaproposal = FillDataRecord(_dt);
            }
            return _etiqaproposal;
        }

        public EtiqaProposal GetPAMoreDetail(string OrderNo)
        {
            EtiqaProposal _etiqaproposal = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "[sprocPAProposalSelectSingleItemEtiqaByOrderNo]";

            SortedList _spara = new SortedList();
            _spara.Add("@OrderNo", OrderNo);

            DataTable _dt = _db.GetDataTableBySP(_spName, _spara);
            if (_dt != null)
            {
                _etiqaproposal = new EtiqaProposal();
                _etiqaproposal = FillDataRecord(_dt);
            }
            return _etiqaproposal;
        }

        private EtiqaProposal FillDataRecord(DataTable myDataTable)
        {
            EtiqaProposal myPAProposal = new EtiqaProposal();
            myPAProposal.PAProposalId = Convert.ToInt64(myDataTable.Rows[0]["PAProposalId"].ToString());
            myPAProposal.PAId = Convert.ToInt64(myDataTable.Rows[0]["PAId"].ToString());
            myPAProposal.PACompanyId = Convert.ToInt64(myDataTable.Rows[0]["PACompanyId"].ToString());
            myPAProposal.PAInsId = Convert.ToInt64(myDataTable.Rows[0]["PAInsId"].ToString());
            myPAProposal.Salutation = myDataTable.Rows[0]["Salutation"].ToString();
            myPAProposal.Name = myDataTable.Rows[0]["Name"].ToString();
            myPAProposal.PostalCode = myDataTable.Rows[0]["PostalCode"].ToString();
            myPAProposal.UnitNo = myDataTable.Rows[0]["UnitNo"].ToString();
            myPAProposal.HomeNo = myDataTable.Rows[0]["HomeNo"].ToString();
            myPAProposal.Address = myDataTable.Rows[0]["Address"].ToString();
            myPAProposal.Gender = myDataTable.Rows[0]["Gender"].ToString();
            myPAProposal.NRICNo = myDataTable.Rows[0]["NRICNo"].ToString();
            myPAProposal.Nationality = myDataTable.Rows[0]["Nationality"].ToString();
            myPAProposal.MobileNo = myDataTable.Rows[0]["MobileNo"].ToString();
            myPAProposal.OfficePhoneNo = myDataTable.Rows[0]["OfficePhoneNo"].ToString();
            myPAProposal.HomePhoneNo = myDataTable.Rows[0]["HomePhoneNo"].ToString();
            myPAProposal.Email = myDataTable.Rows[0]["HomePhoneNo"].ToString();
            myPAProposal.NatureofBusiness = myDataTable.Rows[0]["NatureofBusiness"].ToString();
            myPAProposal.CompanyROCNo = myDataTable.Rows[0]["CompanyROCNo"].ToString();
            myPAProposal.SpouseName = myDataTable.Rows[0]["SpouseName"].ToString();
            myPAProposal.SpouseGender = myDataTable.Rows[0]["SpouseGender"].ToString();
            myPAProposal.SpouseNRICNo = myDataTable.Rows[0]["SpouseNRICNo"].ToString();
            myPAProposal.SpouseNationality = myDataTable.Rows[0]["SpouseNationality"].ToString();
            myPAProposal.SpouseRelation = myDataTable.Rows[0]["SpouseRelation"].ToString();
            myPAProposal.HasPhysicalDefects = Convert.ToBoolean(myDataTable.Rows[0]["HasPhysicalDefects"].ToString());
            myPAProposal.PhysicalDefects = myDataTable.Rows[0]["PhysicalDefects"].ToString();
            myPAProposal.HasEngagedSportsActivities = Convert.ToBoolean(myDataTable.Rows[0]["HasEngagedSportsActivities"].ToString());
            myPAProposal.EngagedSportsActivities = myDataTable.Rows[0]["EngagedSportsActivities"].ToString();
            myPAProposal.HasInsuranceCancelled = Convert.ToBoolean(myDataTable.Rows[0]["HasInsuranceCancelled"].ToString());
            myPAProposal.InsuranceCancelledDetails = myDataTable.Rows[0]["InsuranceCancelledDetails"].ToString();
            myPAProposal.PaymentType = myDataTable.Rows[0]["PamentType"].ToString();
            myPAProposal.OrderNo = myDataTable.Rows[0]["OrderNo"].ToString();
            myPAProposal.OrderSerialNo = Convert.ToInt64(myDataTable.Rows[0]["OrderSerialNo"].ToString());
            myPAProposal.PaymentStatus = Convert.ToBoolean(myDataTable.Rows[0]["PaymentStatus"].ToString());
            myPAProposal.QuoteRefNo = myDataTable.Rows[0]["QuoteRefNo"].ToString();
            myPAProposal.ViewedDetailId = Convert.ToInt64(myDataTable.Rows[0]["ViewedDetailId"].ToString());

            myPAProposal.RowGUID = myDataTable.Rows[0]["RowGUID"].ToString();
            myPAProposal.RefCEUserId = Convert.ToInt64(myDataTable.Rows[0]["RefCEUserId"].ToString());
            myPAProposal.Status = Convert.ToBoolean(myDataTable.Rows[0]["Status"].ToString());
            return myPAProposal;
        }

        public DataSet GetPAPolicyDetails(Int64 PAId, Int64 CEId, Int64 CXCoverTypeId, Int64 ProviderCompanyId)
        {
            DataSet _result = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocCarInsuranceSelectedPlanSingleItem";

            SortedList _spara = new SortedList();
            _spara.Add("@PAId", PAId);
            _spara.Add("@RefCEUserId", CEId);
            _spara.Add("@CITempResultId", CXCoverTypeId);
            _spara.Add("@ProviderCompanyId", ProviderCompanyId);


            using (DataSet _ds = _db.GetDataSet(_spName, _spara))
            {
                if (_ds != null && _ds.Tables[0].Rows.Count > 0)
                {
                    _result = _ds;
                }
            }
            return _result;
        }

        public DataSet GetPolicyDetails(string OrderNo)
        {
            DataSet _result = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocPAProposalSelectItemtoPDF_Etiqa";

            SortedList _spara = new SortedList();
            _spara.Add("@OrderNo", OrderNo);

            _result = new DataSet();
            using (DataSet _ds = _db.GetDataSet(_spName, _spara))
            {
                if (_ds != null && _ds.Tables[0].Rows.Count > 0)
                {
                    _result = _ds;
                }
            }
            return _result;
        }

        public DataSet GetPremiumDetails(string OrderNo)
        {
            DataSet _result = null;
            MyDatabase _db = new MyDatabase();
            string _spName = "sprocPAProposalPremiumSelectItemtoPDF_Etiqa";

            SortedList _spara = new SortedList();
            _spara.Add("@OrderNo", OrderNo);

            _result = new DataSet();
            using (DataSet _ds = _db.GetDataSet(_spName, _spara))
            {
                if (_ds != null && _ds.Tables[0].Rows.Count > 0)
                {
                    _result = _ds;
                }
            }
            return _result;
        }
    }
}
